﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace WebOptiDemo
{
    public class EquiposRepository
    {
        SqlConnection cnx;
        public EquiposRepository()
        {
            cnx = new SqlConnection();
            cnx.ConnectionString = ConfigurationManager.ConnectionStrings["cnx"].ConnectionString;

        }
        
        public List<EquipoCombo> GetFromEquipoCombo()
        {
            
            SqlCommand command = new SqlCommand("GetFromEquipoXML", cnx);
            command.CommandType = CommandType.StoredProcedure;
           List<EquipoCombo> lista = null;
            string xml = "";

            cnx.Open();

            using (SqlDataReader reader = command.ExecuteReader()   )
            {
                while(reader.Read())
                {
                    xml = reader.GetString(0); 
                }

                xml = "<root>" + xml + "</root>";

                var query = from e in XElement.Parse(xml).Descendants("EQUIPO")
                            select  new EquipoCombo
                            { 
                                IdEquipo= Int32.Parse( e.Attribute("IdEquipo").Value),
                                Placa = e.Attribute("NroPlaca").Value
                            };
               
                lista = query.ToList();
                
            }

            cnx.Close();

            return lista;
        
        }

        public List<Catalogo> GetCatalogoCombo()
        {
           SqlCommand command = new SqlCommand("GetFromCatalogoXML", cnx);
            command.CommandType = CommandType.StoredProcedure;
            List<Catalogo> lista = null;
            string xml = "";

            cnx.Open();

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    xml = reader.GetString(0);
                }

                xml = "<root>" + xml + "</root>";

                var query = from c in XElement.Parse(xml).Descendants("CATALAGO")
                            select new Catalogo
                            {
                                IdCatalogo = Int32.Parse(c.Attribute("IdCatalogo").Value),
                                NroParte = c.Attribute("NroPartePrincipal").Value,
                                Descripcion = c.Attribute("DescParte").Value
                            };

                lista = query.ToList();                   

            }

            cnx.Close();

            return lista;
        }

        public List<Catalogo> GetCatalogoSinXML()
        {
            SqlCommand command = new SqlCommand("GetFromCatalogo", cnx);
            command.CommandType = CommandType.StoredProcedure;
            List<Catalogo> lista = new List<Catalogo>();

            cnx.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Catalogo catalogo = new Catalogo() 
                    {
                        IdCatalogo = reader.GetInt32(0),
                        NroParte = reader.GetString(1),
                        Descripcion = reader.GetString(2)
                    };

                    lista.Add(catalogo);
                }
            }
            cnx.Close();

            return lista;
        }
    }
}
